import xlrd_compdoc_commented as xlrd
import numpy as np
import copy
import xlwt

# 使用说明：
#   输入文件在'./origin'路径下
#   输出文件在'./output'路径下
#   3点地方需要设置【输入文件路径】【输入sheet索引】【输出文件路径】均已在对应行注释
#   请 crtl+f 输入【设置】查找
#    运行请安装相关python库
# 注意：
#   本程序只用于处理预测sheet，不可用于处理实测sheet
#   xlrd库最新版不可读取.xlsx格式文件会报错
#   xlrd不要安装最新版，本程序xlrd版本为1.20,搜索【xlrd_compdoc_commented】安装即可
#   xlwt库为最新版，只可保存为.xls文件格式，保存.xlsx格式会报错


# affix1 = xlrd.open_workbook('./origin/附件1 监测点A空气质量预报基础数据.xlsx')  # 设置输入文件路径
# affix1 = xlrd.open_workbook('./origin/附件2 监测点B、C空气质量预报基础数据.xlsx')
affix1 = xlrd.open_workbook('./origin/附件3 监测点A1、A2、A3空气质量预报基础数据.xlsx')
# affix1 = xlrd.open_workbook('./origin/1.xlsx')
predict = affix1.sheet_by_index(2)  # 设置输入sheet索引
dataRow = int(predict.nrows)
dataCol = int(predict.ncols)
currentRow = 1
variable = locals()
predictList = []
# 空气指数和污染物限值
air = [0, 50, 100, 150, 200, 300, 400, 500]
co = [0, 2, 4, 14, 24, 36, 48, 60]
so2 = [0, 50, 150, 475, 800, 1600, 2100, 2620]
no2 = [0, 40, 80, 180, 280, 565, 750, 940]
o3 = [0, 100, 160, 215, 265, 800]
pm10 = [0, 50, 150, 250, 350, 420, 500, 600]
pm25 = [0, 35, 75, 115, 150, 250, 350, 500]
indicator = [co, so2, no2, o3, pm10, pm25, air]


def air_pollution(aqi):
    if 0 <= aqi <= 50:
        return '优'
    elif 51 <= aqi <= 100:
        return '良'
    elif 101 <= aqi <= 150:
        return '轻度污染'
    elif 151 <= aqi <= 200:
        return '中度污染'
    elif 201 <= aqi <= 300:
        return '重度污染'
    elif 301 <= aqi:
        return '严重污染'


class Predict:
    global predict

    def __init__(self, row):
        self.row = row
        self.dayList = []
        self.exDate = str(xlrd.xldate_as_datetime(predict.cell(self.row, 0).value, 0)).split(' ')[0]  # 执行预测日期
        self.location = predict.cell(self.row, 2).value
        # 初始化生命变量
        self.dayData = {}
        self.preDate = ''
        self.hourList = []
        self.hourData = {}
        # 遍历创建dayList
        for k in range(0, 3):
            # self.row = k * 24
            self.set_day_data(self.row)
            self.row += 24

        # print(self.dayData)

    def set_day_data(self, row):
        global predict
        for j in range(row, row + 24):
            self.hourData = self.set_hour_data(j)  # 预测小时内容
            self.hourList.append(self.hourData)  # 封装小时数据
        self.preDate = str(xlrd.xldate_as_datetime(predict.cell(row, 1).value, 0)).split(' ')[0]
        self.dayData['预测日期'] = self.preDate
        self.dayData['地点'] = predict.cell(row, 2).value
        self.dayData['每小时预测内容'] = self.hourList
        self.compute_ave(self.hourList, self.dayData)
        self.compute_ave_o3(self.hourList, self.dayData)
        self.get_iaqi(self.dayData)
        dict_temp = copy.deepcopy(self.dayData)
        self.dayList.append(dict_temp)
        # 还原temp参数
        # self.row += 24
        self.hourData = {}
        self.hourList = []
        return self.dayData

    def set_hour_data(self, row):
        global predict
        hour = str(xlrd.xldate_as_datetime(predict.cell(row, 1).value, 0)).split(' ')[1].split(':')[0]  # xlsx时间格式转换
        self.hourData = {predict.cell(0, 1).value: hour}
        for i in range(2, dataCol):
            if predict.cell(0, i).value == '预测时间' or predict.cell(0, i).value == '':
                continue
            elif predict.cell(row, i).value == 'NA':
                continue
            else:
                self.hourData[predict.cell(0, i).value] = predict.cell(row, i).value
        return self.hourData

    def compute_ave(self, hour_list: list, day_data: dict):
        key_list = []
        # 获取key列表
        for key in hour_list[0]:
            value_type = type(hour_list[0][key])
            if value_type == int or value_type == float:
                if 'O3' not in key:
                    key_list.append(key)
        # print(key_list)
        for a in range(0, len(key_list)):
            data = []
            key = key_list[a]
            for b in range(0, len(hour_list)):
                data.append(hour_list[b][key])
            ave = np.mean(data)
            if '小时平均' in key:
                key = key.replace('小时平均', '')
            day_data['日平均' + key] = ave
        # print(self.dayData)

    def compute_ave_o3(self, hour_list: list, day_data: dict):
        o3_data = []
        hours8_ave = []
        sum = 0
        for i in range(0, 24):
            o3_data.append(hour_list[i]['O3小时平均浓度(μg/m³)'])
        for j in range(0, 17):
            for k in range(0, 8):
                sum += o3_data[j + k]
            hours8_ave.append(sum / 8)
            sum = 0
        day_data['8小时最大平均O3浓度(μg/m³)'] = max(hours8_ave)
        day_data['8小时平均O3浓度(μg/m³)'] = hours8_ave

    def get_iaqi(self, day_data: dict):
        global indicator
        iaqi_list = {}
        iaqi_value = []
        new_key_list = []
        key_list = ['日平均CO浓度(mg/m³)', '日平均SO2浓度(μg/m³)', '日平均NO2浓度(μg/m³)', '8小时最大平均O3浓度(μg/m³)', '日平均PM10浓度(μg/m³)',
                    '日平均PM2.5浓度(μg/m³)', ]
        for j in range(0, 6):
            cp = day_data[key_list[j]]  # 每日指标
            target = indicator[j]  # 标准指标列表
            new_key_list.append(key_list[j].split('均')[1].split('浓')[0]+'的IAQI')
            for i in range(0, len(target)-1):
                # print(key_list[j],day_data[key_list[j]])
                if target[i] < cp < target[i + 1]:
                    target_index = i
                    # 计算iaqi
                    bph = target[target_index+1]  # 高限值
                    bpl = target[target_index]  # 低限值
                    iaqih = indicator[6][target_index+1]  # 空气高限值
                    iaqil = indicator[6][target_index]  # 空气低限值
                    iaqi = int((iaqih-iaqil)/(bph-bpl)*(cp-bpl)+iaqil)
                    new_key = key_list[j].split('均')[1].split('浓')[0]+'的IAQI'
                    iaqi_list[new_key] = iaqi
                    iaqi_value.append(iaqi)
        # print(new_key_list)
        if day_data[key_list[3]] > 800:
            msg = '8小时最大平均O3浓度>800μg/m³，不进行空气质量分指数计算'
            day_data['IAQI值'] = msg
            day_data['AQI值'] = msg
            day_data['空气质量'] = msg
            day_data['首要污染物'] = msg
        else:
            aqi = max(iaqi_value)
            most_pollution = []
            aqi_line = []
            over_pollution = []
            for k in range(0, len(iaqi_list)):
                if iaqi_list[new_key_list[k]] == aqi:
                    line = indicator[k][5] if (k == 3) else indicator[k][7]
                    new_key = key_list[k].split('均')[1].split('浓')[0]
                    if aqi <= line:
                        most_pollution.append(new_key)
                    else:
                        over_pollution.append(new_key)
                        aqi_line.append(line)
            if len(over_pollution) > 0:
                msg = over_pollution[0]+'高于限值'+str(aqi_line[0])+'，不进行空气质量分指数计算'
                day_data['IAQI值'] = msg
                day_data['AQI值'] = msg
                day_data['空气质量'] = msg
                day_data['首要污染物'] = msg
            else:
                day_data['IAQI值'] = iaqi_list
                day_data['AQI值'] = aqi
                day_data['空气质量'] = air_pollution(aqi)
                if aqi < 50:
                    day_data['首要污染物'] = '当天无首要污染物'
                else:
                    day_data['首要污染物'] = str(most_pollution).replace('\'', '').replace('[', '').replace(']', '')


for times in range(0, int((dataRow - 1) / 72)):
    # currentRow += times * 72
    predictList.append(Predict(currentRow))
    currentRow += 72
# 写入文件
workbook = xlwt.Workbook(encoding='ascii')
output = workbook.add_sheet('监测点A逐日污染物浓度与气象一次预报数据')
writeRow = 1
output.write(0, 0, '预测执行日期')
output.write(0, 1, '监测地点')
# 写表头
count = 2
for key in predictList[0].dayList[0]:
    if key == '每小时预测内容' or key == '8小时平均O3浓度(μg/m³)':
        continue
    if key == 'IAQI值':
        for i in predictList[0].dayList[0][key]:
            output.write(0, count, i)
            count += 1
        continue
    # print(key)
    output.write(0, count, key)
    count += 1
# 写入内容
for i in range(0, len(predictList)):
    output.write(writeRow, 0, predictList[i].exDate)
    output.write(writeRow, 1, predictList[i].location)
    for j in range(0, len(predictList[i].dayList)):
        count = 2
        dayDataTemp = predictList[i].dayList[j]
        for key in dayDataTemp:
            if key == '每小时预测内容' or key == '8小时平均O3浓度(μg/m³)':
                continue
            if key == 'IAQI值':
                if type(dayDataTemp[key]) == str:
                    output.write(writeRow, count, dayDataTemp[key])
                    count += 1
                else:
                    for k in dayDataTemp[key]:
                        # print(dayDataTemp[key])
                        output.write(writeRow, count, dayDataTemp[key][k])
                        count += 1
                    continue
            output.write(writeRow, count, dayDataTemp[key])
            count += 1
        writeRow += 1


# workbook.save('./output/predict/A.xls')  # 设置输出文件路径
# workbook.save('./output/predict/B.xls')  # 设置输出文件路径
# workbook.save('./output/predict/C.xls')  # 设置输出文件路径
# workbook.save('./output/predict/A1.xls')  # 设置输出文件路径
# workbook.save('./output/predict/A2.xls')  # 设置输出文件路径
workbook.save('./output/A3.xls')  # 设置输出文件路径
print('写入完成')

# print('预测执行时间：', dir(predictList[0]))
# print(len(predictList))
# # print('预测执行时间：', predictList[0].exDate)
# # print('预测地点：', predictList[0].location)
# print('预测内容：', predictList[0].dayList)

